package com.icim.dao;



import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.icim.entity.Ret_cus_box;
import com.icim.entity.Ret_cus_prd_in;

@Repository
public class RetCusBoxDao {
    @Resource
    private JdbcTemplate icimJdbcTemplate;

    public void save(Ret_cus_box box) {
        StringBuffer sqlBuffer = new StringBuffer("INSERT INTO RET_CUS_BOX(");
        sqlBuffer.append("BOXID,CELLSIZE,ITEMNO,MODEL_NO,MODULE_SITE,");
        sqlBuffer.append("CELLSITE,PALLET_NO,BOXNO,ARRAYPARTNO,PRODUCT_QTY,");
        sqlBuffer.append("EVT_TIMESTAMP");
        sqlBuffer.append(") VALUES(");
        sqlBuffer.append("?,?,?,?,?,");
        sqlBuffer.append("?,?,?,?,?,");
        sqlBuffer.append("?");
        sqlBuffer.append(")");

        icimJdbcTemplate.update(
                sqlBuffer.toString(),
                new Object[] { box.getBoxid(),box.getCellsize(),box.getItemno(),box.getModel_no(),box.getModule_site(),
                        box.getCellsite(),box.getPallet_no(),box.getBoxno(),box.getArraypartno(),box.getProduct_qty(),box.getEvt_timestamp() });
    };
    
    public List<Ret_cus_box> getMtrlBoxList(List<String> boxIdList) {
      StringBuffer sqlBuffer = new StringBuffer( "SELECT BOXID,CELLSIZE,ITEMNO,MODEL_NO,MODULE_SITE,CELLSITE,PALLET_NO,BOXNO,ARRAYPARTNO,PRODUCT_QTY,EVT_TIMESTAMP FROM RET_CUS_BOX WHERE BOXID IN (");
      for (String boxId : boxIdList) {
          sqlBuffer.append("'").append(boxId).append("'");
          if(boxIdList.indexOf(boxId)< boxIdList.size()-1){
              sqlBuffer.append(",");
          }
      }
      sqlBuffer.append(")");
      return icimJdbcTemplate.query(sqlBuffer.toString(), new RowMapper<Ret_cus_box>() {
          @Override
          public Ret_cus_box mapRow(ResultSet rs, int rowNum) throws SQLException {
            Ret_cus_box retCusBox = new Ret_cus_box();
            //BOXID,CELLSIZE,ITEMNO,MODEL_NO,MODULE_SITE,CELLSITE,PALLET_NO,BOXNO,ARRAYPARTNO
            retCusBox.setBoxid(rs.getString("BOXID"));
            retCusBox.setCellsize(rs.getString("CELLSIZE"));
            retCusBox.setItemno(rs.getString("ITEMNO"));
            retCusBox.setModel_no(rs.getString("MODEL_NO"));
            retCusBox.setModule_site(rs.getString("MODULE_SITE"));
            retCusBox.setCellsite(rs.getString("CELLSITE"));
            retCusBox.setPallet_no(rs.getString("PALLET_NO"));
            retCusBox.setBoxno(rs.getString("BOXNO"));
            retCusBox.setArraypartno(rs.getString("ARRAYPARTNO"));
            retCusBox.setProduct_qty(rs.getString("PRODUCT_QTY"));
            retCusBox.setEvt_timestamp(rs.getString("EVT_TIMESTAMP"));
            return retCusBox;
          }
      });

    }
    
    public List<Ret_cus_box> getMtrlBox(String boxId) {
        String hql = "SELECT BOXID,CELLSIZE,ITEMNO,MODEL_NO,MODULE_SITE,CELLSITE,PALLET_NO,BOXNO,ARRAYPARTNO,PRODUCT_QTY,EVT_TIMESTAMP FROM RET_CUS_BOX WHERE BOXID = '"+boxId+"'";
        return icimJdbcTemplate.query(hql, new RowMapper<Ret_cus_box>() {
            @Override
            public Ret_cus_box mapRow(ResultSet rs, int rowNum) throws SQLException {
              Ret_cus_box retCusBox = new Ret_cus_box();
              //BOXID,CELLSIZE,ITEMNO,MODEL_NO,MODULE_SITE,CELLSITE,PALLET_NO,BOXNO,ARRAYPARTNO
              retCusBox.setBoxid(rs.getString("BOXID"));
              retCusBox.setCellsize(rs.getString("CELLSIZE"));
              retCusBox.setItemno(rs.getString("ITEMNO"));
              retCusBox.setModel_no(rs.getString("MODEL_NO"));
              retCusBox.setModule_site(rs.getString("MODULE_SITE"));
              retCusBox.setCellsite(rs.getString("CELLSITE"));
              retCusBox.setPallet_no(rs.getString("PALLET_NO"));
              retCusBox.setBoxno(rs.getString("BOXNO"));
              retCusBox.setArraypartno(rs.getString("ARRAYPARTNO"));
              retCusBox.setProduct_qty(rs.getString("PRODUCT_QTY"));
              retCusBox.setEvt_timestamp(rs.getString("EVT_TIMESTAMP"));
              return retCusBox;
            }
        });

    }
}
